﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;

using MyEntLibCMS.Model;
using MyEntLibCMS.IDAL;
using MyEntLibCMS.Utility;

namespace MyEntLibCMS.DAL
{
    public class UserRepository : IUsersRepository
    {
        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns>用户实体信息</returns>
        public User GetUserByUserID(int userID)
        {
            User user = null;
            Database db = DBHelper.CreateDataBase();
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from Users ");
            sql.Append(" where UserID=@UserID");
            DbCommand cmd = db.GetSqlStringCommand(sql.ToString());
            db.AddInParameter(cmd, "@UserID", DbType.Int32, userID);
            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                if (reader.Read())
                {
                    user = new User()
                    {
                        UserID = (int)reader["UserID"],
                        UserName = (string)reader["UserName"],
                        Sex = (bool)reader["Sex"],
                        BirthDate = (DateTime)reader["BirthDate"],
                        Office = (string)reader["Office"],
                        PhotosUrl = (string)reader["PhotosUrl"],
                        Remarks = (string)reader["Remarks"],
                        Status = (bool)reader["Status"]
                    };
                }
            }
            return user;
        }

        /// <summary>
        /// 创建用户信息
        /// </summary>
        /// <param name="user">用户实体信息</param>
        /// <param name="login">登录实体信息 </param>
        /// <returns>ID</returns>
        public int Create(User user, Login login)
        {
            int userId = 0;
            Database db = DBHelper.CreateDataBase();
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into Users (UserName,Sex,Office,BirthDate,PhotosUrl,Status,Remarks)");
            sql.Append(" values(@UserName,@Sex,@Office,@BirthDate,@PhotosUrl,@Status,@Remarks); select @@IDENTITY;");
            DbCommand cmd = db.GetSqlStringCommand(sql.ToString());
            db.AddInParameter(cmd, "@UserName", DbType.String, user.UserName);
            db.AddInParameter(cmd, "@Sex", DbType.String, user.Sex);
            db.AddInParameter(cmd, "@Office", DbType.String, user.Office);
            db.AddInParameter(cmd, "@BirthDate", DbType.String, user.BirthDate);
            db.AddInParameter(cmd, "@PhotosUrl", DbType.String, user.PhotosUrl);
            db.AddInParameter(cmd, "@Status", DbType.String, user.Status);
            db.AddInParameter(cmd, "@Remarks", DbType.String, user.Remarks);
            using (DbConnection conn = db.CreateConnection())
            {
                BaseRepository trans = new BaseRepository(conn);
                trans.BeginTransaction();
                try
                {
                    userId = Convert.ToInt32(db.ExecuteScalar(cmd));
                    login.UserID = userId;
                    StringBuilder sql2 = new StringBuilder();
                    sql.Append("insert into Login(UserID,LoginAccount,Password,Status) values(@UserID,@LoginAccount,@Password,@Status)");
                    DbCommand cmd2 = db.GetSqlStringCommand(sql2.ToString());
                    db.AddInParameter(cmd, "@UserID", DbType.Int32, login.UserID);
                    db.AddInParameter(cmd, "@LoginAccount", DbType.String, login.LoginAccount);
                    db.AddInParameter(cmd, "@Password", DbType.String, login.Password);
                    db.AddInParameter(cmd, "@Status", DbType.Boolean, login.Status);
                    db.ExecuteNonQuery(cmd2);
                    trans.Commit();
                }
                catch
                {
                    trans.Rollback();
                }
            }
            return userId;
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="user">用户实体信息</param>
        /// <returns>TRUE OR FALSE</returns>
        public bool Update(User user)
        {
            Database db = DBHelper.CreateDataBase();
            StringBuilder sql = new StringBuilder();
            sql.Append("Update Users Set UserName=@UserName,");
            sql.Append("Sex=@Sex,");
            sql.Append("Office=@Office,");
            sql.Append("BirthDate=@BirthDate,");
            sql.Append("PhotosUrl=@PhotosUrl,");
            sql.Append("Remarks=@Remarks");
            sql.Append(" where UserID=@UserID");
            DbCommand cmd = db.GetSqlStringCommand(sql.ToString());
            db.AddInParameter(cmd, "@UserName", DbType.String, user.UserName);
            db.AddInParameter(cmd, "@Sex", DbType.String, user.Sex);
            db.AddInParameter(cmd, "@Office", DbType.String, user.Office);
            db.AddInParameter(cmd, "@BirthDate", DbType.String, user.BirthDate);
            db.AddInParameter(cmd, "@PhotosUrl", DbType.String, user.PhotosUrl);
            db.AddInParameter(cmd, "@Remarks", DbType.String, user.Remarks);
            db.AddInParameter(cmd, "@UserID", DbType.Int32, user.UserID);
            return db.ExecuteNonQuery(cmd) > 0 ? true : false;
        }

        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns>TRUE OR FALSE</returns>
        public bool Delete(int userID)
        {
            Database db = DBHelper.CreateDataBase();
            StringBuilder sql1 = new StringBuilder();
            sql1.Append("update Users set Status=0 where UserID=@UserID");
            DbCommand com1 = db.GetSqlStringCommand(sql1.ToString());
            db.AddInParameter(com1, "@UserID", DbType.Int32, userID);
            StringBuilder sql2 = new StringBuilder();
            sql2.Append("update Login set Status=0 where UserID=@UserID");
            DbCommand com2 = db.GetSqlStringCommand(sql2.ToString());
            db.AddInParameter(com2, "@UserID", DbType.Int32, userID);
            using (DbConnection conn = db.CreateConnection())
            {
                BaseRepository trans = new BaseRepository(conn);
                trans.BeginTransaction();
                try
                {
                    //修改用户有效状态
                    db.ExecuteNonQuery(com1, trans.trans);
                    //修改账号有效状态
                    db.ExecuteNonQuery(com2, trans.trans);
                    trans.Commit();
                    return true;
                }
                catch
                {
                    trans.Rollback();
                    return false;
                }
            }
        }

        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <returns>用户集合</returns>
        public IList<User> GetAllUser()
        {
            List<User> list = new List<User>();
            Database db = DBHelper.CreateDataBase();
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from Users where Status=1 order by UserID desc ");
            DbCommand cmd = db.GetSqlStringCommand(sql.ToString());

            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    list.Add(new User()
                    {
                        UserID =(int) reader["UserID"],
                        UserName = (string)reader["UserName"],
                        BirthDate = (DateTime)reader["BirthDate"],
                        Office = (string)reader["Office"],
                        PhotosUrl = (string)reader["PhotosUrl"],
                        Remarks = (string)reader["Remarks"],
                        Sex = (bool)reader["Sex"],
                        Status = (bool)reader["Status"]
                    });
                }
            }
            return list;
        }
    }
}
